package org.termine.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;

import org.termine.entity.Service;
import org.termine.entity.ServiceSelectItem;

@ManagedBean
@ApplicationScoped
public class ServiceDAO extends BaseDAO {

	public void addService(Connection connection, Service service, Integer providerId)
			throws Exception {
		PreparedStatement statement = null;
		String statementString = "insert into SERVICE (P_ID, S_NAME, S_DESCRIPTION, "
				+ "S_DURATION_IN_MIN, S_BOOK_DEADLINE_IN_MIN,S_CANCEL_DEADLINE_IN_MIN, "
				+ " S_START, S_END) values (?,?,?,?,?,?,?,?)";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			statement.setString(2, service.getName());
			statement.setString(3, service.getDescription());
			statement.setInt(4, service.getDuration_in_min());
			statement.setInt(5, service.getBook_deadline_in_min());
			statement.setInt(6, service.getCancel_deadline_in_min());
			statement.setTimestamp(7, new Timestamp(service.getStart().getTime()));
			statement.setTimestamp(8, new Timestamp(service.getEnd().getTime()));
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}
	
	public Integer getNumberOfCorrespondingAppointments(Connection connection,
			Service service) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select count(*) as X from APPOINTMENT where S_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, service.getService_id());
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				return new Integer(resultSet.getInt("X"));
			} else {
				return null;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public boolean overlapsWithExistingService(Connection connection, Service service,
			Integer providerId) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select count(*) as X from SERVICE where (S_START <= ?) "
				+ "and (? <= S_END) and P_ID = ? and S_NAME = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setTimestamp(1, new Timestamp(service.getEnd().getTime()));
			statement.setTimestamp(2, new Timestamp(service.getStart().getTime()));
			statement.setInt(3, providerId);
			statement.setString(4, service.getName());
			ResultSet resultSet = statement.executeQuery();
			resultSet.next();
			return resultSet.getInt("X") != 0;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public void deleteService(Connection connection, Service service) throws Exception {
		PreparedStatement statement = null;
		String statementString = "delete from SERVICE where S_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, service.getService_id());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public List<Service> getAllServicesForProvider(Connection connection, Integer providerId)
			throws Exception {
		PreparedStatement statement = null;
		String statementString = "select * from SERVICE where P_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, providerId);
			ResultSet resultSet = statement.executeQuery();
			List<Service> result = new LinkedList<Service>();
			while (resultSet.next()) {
				Service service = new Service();
				Integer integer;
				String string;
				Date date;

				integer = new Integer(resultSet.getInt("S_ID"));
				if (!resultSet.wasNull()) {
					service.setService_id(integer);
				}
				integer = new Integer(resultSet.getInt("P_ID"));
				if (!resultSet.wasNull()) {
					service.setProvider_id(integer);
				}
				string = resultSet.getString("S_NAME");
				if (!resultSet.wasNull()) {
					service.setName(string);
				}
				string = resultSet.getString("S_DESCRIPTION");
				if (!resultSet.wasNull()) {
					service.setDescription(string);
				}
				integer = new Integer(resultSet.getInt("S_DURATION_IN_MIN"));
				if (!resultSet.wasNull()) {
					service.setDuration_in_min(integer);
				}
				integer = new Integer(resultSet.getInt("S_BOOK_DEADLINE_IN_MIN"));
				if (!resultSet.wasNull()) {
					service.setBook_deadline_in_min(integer);
				}
				integer = new Integer(resultSet.getInt("S_CANCEL_DEADLINE_IN_MIN"));
				if (!resultSet.wasNull()) {
					service.setCancel_deadline_in_min(integer);
				}
				date = new Date(resultSet.getTimestamp("S_START").getTime());
				if (!resultSet.wasNull()) {
					service.setStart(date);
				}
				date = new Date(resultSet.getTimestamp("S_END").getTime());
				if (!resultSet.wasNull()) {
					service.setEnd(date);
				}
				result.add(service);
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public List<ServiceSelectItem> getAllAvailableServices(Connection connection,
			Integer providerId, Date now) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select * from SERVICE s join PROVIDER p on s.P_ID = p.P_ID "
				+ "join PERSONAL_DATA pd on p.PD_ID = pd.PD_ID where s.S_END >= ?";
		if (providerId != null) {
			statementString += " and s.P_ID != ?";
		}
		try {
			statement = connection.prepareStatement(statementString);
			statement.setTimestamp(1, new Timestamp(now.getTime()));
			if (providerId != null) {
				statement.setInt(3, providerId);
			}
			ResultSet resultSet = statement.executeQuery();
			List<ServiceSelectItem> result = new LinkedList<ServiceSelectItem>();
			while (resultSet.next()) {
				ServiceSelectItem service = new ServiceSelectItem();
				Integer integer;
				String string;
				Date date;

				integer = new Integer(resultSet.getInt("P_ID"));
				if (!resultSet.wasNull()) {
					service.setProviderId(integer);
				}
				integer = new Integer(resultSet.getInt("S_ID"));
				if (!resultSet.wasNull()) {
					service.setServiceId(integer);
				}
				string = resultSet.getString("S_NAME");
				if (!resultSet.wasNull()) {
					service.setName(string);
				}
				string = resultSet.getString("S_DESCRIPTION");
				if (!resultSet.wasNull()) {
					service.setDescription(string);
				}
				integer = new Integer(resultSet.getInt("S_DURATION_IN_MIN"));
				if (!resultSet.wasNull()) {
					service.setDurationInMin(integer);
				}
				integer = new Integer(resultSet.getInt("S_BOOK_DEADLINE_IN_MIN"));
				if (!resultSet.wasNull()) {
					service.setBookDeadlineInMin(integer);
				}
				integer = new Integer(resultSet.getInt("S_CANCEL_DEADLINE_IN_MIN"));
				if (!resultSet.wasNull()) {
					service.setCancelDeadlineInMin(integer);
				}
				string = resultSet.getString("PD_FORENAME");
				if (!resultSet.wasNull()) {
					service.setForename(string);
				}
				string = resultSet.getString("PD_SURNAME");
				if (!resultSet.wasNull()) {
					service.setSurname(string);
				}
				string = resultSet.getString("PD_PHONE_NUMBER");
				if (!resultSet.wasNull()) {
					service.setPhoneNumber(string);
				}
				string = resultSet.getString("PD_EMAIL");
				if (!resultSet.wasNull()) {
					service.setEmail(string);
				}
				date = new Date(resultSet.getTimestamp("S_START").getTime());
				if (!resultSet.wasNull()) {
					service.setStart(date);
				}
				date = new Date(resultSet.getTimestamp("S_END").getTime());
				if (!resultSet.wasNull()) {
					service.setEnd(date);
				}
				result.add(service);
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}
}
